# encoding: utf-8
# @author: wangxin
# @software: garner
# @file: exportBusinessReportViesws.py
# @time: 2025/2/17 10:07
import time
from http.client import responses

import pymysql
from django.http import HttpResponse, JsonResponse
from django.views import View
#使用pandas处理数据
import  pandas as pd

# 导入StreamingHttpResponse类，用于处理需要流式响应的HTTP请求
from django.http import  StreamingHttpResponse

class exportBusinessReportp(View):
    def file_iterator(self, file_path):
        with open(file_path, 'rb') as f:
                while True:
                    # 读取文件内容，每次读取1024字节
                    data = f.read(1024)
                    # 如果读取到文件末尾，则返回None
                    if not data:
                        break
                    # 返回读取到的数据
                    yield data
    def get(self,request):

        Report_dict=getReportDate()
        # print(Report_dict)
        hot_setmeak_df = pd.DataFrame(Report_dict['hotSetmeal'])
        other_data ={k:v for k,v in Report_dict.items() if k!='hotSetmeal'}
        other_data_df=pd.DataFrame([other_data])
        #创建一个writer对象
        with pd.ExcelWriter('d:\\rePort.xlsx') as writer:
            hot_setmeak_df.to_excel(writer, sheet_name='套餐占比', index=False)
            other_data_df.to_excel(writer, sheet_name='运营数据', index=False)

        # 创建一个StreamingHttpResponse对象，用于流式传输文件内容
        # 参数1: writer.file_iterator('d:\\rePort.xlsx')，调用writer的file_iterator方法，传入文件路径'd:\\rePort.xlsx'，返回一个迭代器，用于逐块读取文件内容
        # 参数2: content_type='application/octet-stream'，设置响应的内容类型为二进制流
        responses = StreamingHttpResponse(self.file_iterator('d:\\rePort.xlsx'),content_type='application/octet-stream')

        # 设置响应头Content-Disposition，指示浏览器以附件形式下载文件，文件名为'rePort.xlsx'
        responses['Content-Disposition'] = 'attachment;filename="{}"'.format('rePort.xlsx')

        return responses




#获取运营数据
def getReportDate():
        datatime = time.strftime("%Y-%m-%d", time.localtime())
        conn = pymysql.Connect(
            host="127.0.0.1",
            port=3306, user="root",
            password="123456",
            db="ruici",
            charset="utf8",
            autocommit=True
        )
        cursor = conn.cursor()
        sql = """
                   SELECT 
                   s.NAME,
                   o_grouped.order_count,
                   (SELECT COUNT(*) FROM t_order) AS total_orders
               FROM 
                   (SELECT 
                        o.setmeal_id, 
                        COUNT(o.id) AS order_count
                    FROM 
                        t_order AS o 
                    GROUP BY 
                        o.setmeal_id
                   ) AS o_grouped
               INNER JOIN 
                   t_setmeal AS s 
               ON 
                   o_grouped.setmeal_id = s.id;
    
                   """

        cursor.execute(sql)
        res = cursor.fetchall()
        data_list = list()
        reportData_dict = dict()
        for data in res:
            data_dict = dict()
            data_dict['name'] = data[0]
            data_dict['setmeal_count'] = data[1]
            data_dict['proportion'] = round(data[1] / data[2], ndigits=3)
            data_list.append(data_dict)

            reportData_dict['reportDate'] = datatime
            reportData_dict['hotSetmeal'] = data_list
            sql2 = f"""
                   SELECT 
                       COUNT(id) 
                   FROM 
                       t_member 
                   WHERE 
                       regTime = '{datatime}';
                   """
            cursor.execute(sql2)
            todayNewMember = cursor.fetchone()
            reportData_dict['todayNewMember'] = todayNewMember
            sql3 = f"""
                   SELECT 
                       COUNT(id) 
                   FROM 
                       t_member 
    
                   """
            cursor.execute(sql3)
            totalMember = cursor.fetchone()
            reportData_dict['totalMember'] = totalMember
            endTime = time.strftime("%Y-%m-%d", time.localtime())
            startTime = time.strftime("%Y-%m-%d", time.localtime(time.time() - 3600 * 24))
            sql4 = f"""
                   SELECT 
                       COUNT(id) 
                   FROM 
                       t_member 
                   WHERE 
                       regTime >= '{startTime}' AND regTime <= '{endTime}'
                   """
            cursor.execute(sql4)
            thisWeekNewMember = cursor.fetchone()
            reportData_dict['thisWeekNewMember'] = thisWeekNewMember
            startTime = time.strftime("%Y-%m-%d", time.localtime(time.time() - 3600 * 24 * 7))
            endTime = time.strftime("%Y-%m-%d", time.localtime())
            sql5 = f"""
                   SELECT 
                       COUNT(id) 
                   FROM 
                       t_member 
                   WHERE 
                       regTime >= '{startTime}' AND regTime <= '{endTime}'
                   """
            cursor.execute(sql5)
            thisWeekNewMember = cursor.fetchone()
            reportData_dict['thisWeekNewMember'] = thisWeekNewMember
            startTime = time.strftime("%Y-%m-%d", time.localtime(time.time() - 3600 * 24 * 30))

            endTime = time.strftime("%Y-%m-%d", time.localtime())
            sql6 = f"""
                   SELECT 
                       COUNT(id) 
                   FROM 
                       t_member 
                   WHERE 
                       regTime >= '{startTime}' AND regTime <= '{endTime}'
                   """
            cursor.execute(sql6)
            thisMonthNewMember = cursor.fetchone()
            reportData_dict['thisMonthNewMember'] = thisMonthNewMember
            # 预约人数统计

            sql7 = f"""
                   SELECT 
                       COUNT(id) 
                   FROM 
                       t_order 
                   WHERE 
                       orderDate = '{datatime}'
                   """
            cursor.execute(sql7)
            todayOrderNumber = cursor.fetchone()
            reportData_dict['todayOrderNumber'] = todayOrderNumber

            sql8 = f"""
                   SELECT 
                       COUNT(id) 
                   FROM 
                       t_order 
                   WHERE 
                        orderDate = '{datatime}'
                   ANd 
                       orderStatus ='已到诊'    
                   """
            cursor.execute(sql8)
            todayVisitsNumber = cursor.fetchone()
            reportData_dict['todayVisitsNumber'] = todayVisitsNumber
            startTime = time.strftime("%Y-%m-%d", time.localtime(time.time() - 3600 * 24 * 7))
            endTime = time.strftime("%Y-%m-%d", time.localtime())
            sql9 = f"""
                   SELECT 
                       COUNT(id) 
                   FROM 
                       t_order 
                   WHERE 
                        orderDate >= '{startTime}' AND orderDate <= '{endTime}'
    
                   """
            cursor.execute(sql9)
            thisWeekOrderNumber = cursor.fetchone()
            reportData_dict['thisWeekOrderNumber'] = thisWeekOrderNumber

            sql10 = f"""
                   SELECT 
                       COUNT(id) 
                   FROM 
                       t_order 
                   WHERE 
                        orderDate >= '{startTime}' AND orderDate <= '{endTime}'
                   ANd 
                       orderStatus ='已到诊'    
                   """
            cursor.execute(sql10)
            thisWeekVisitsNumber = cursor.fetchone()
            reportData_dict['thisWeekVisitsNumber'] = thisWeekVisitsNumber
            startTime = time.strftime("%Y-%m-%d", time.localtime(time.time() - 3600 * 24 * 30))
            endTime = time.strftime("%Y-%m-%d", time.localtime())
            sql11 = f"""
                   SELECT 
                       COUNT(id) 
                   FROM 
                       t_order 
                   WHERE 
                        orderDate >= '{startTime}' AND orderDate <= '{endTime}'
    
                   """
            cursor.execute(sql11)
            thisMonthOrderNumber = cursor.fetchone()
            reportData_dict['thisMonthOrderNumber'] = thisMonthOrderNumber
            sql12 = f"""
                   SELECT 
                       COUNT(id) 
                   FROM 
                       t_order 
                   WHERE 
                        orderDate >= '{startTime}' AND orderDate <= '{endTime}'
                   ANd 
                       orderStatus ='已到诊'    
                   """
            cursor.execute(sql12)
            thisMonthVisitsNumber = cursor.fetchone()
            reportData_dict['thisMonthVisitsNumber'] = thisMonthVisitsNumber
            return reportData_dict
